﻿Imports System.Windows.Forms
Imports System.ComponentModel
Imports System.Collections.Specialized

Public Class AccountToAccountList
    Private Sub InputUpdate_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Button1_Click(Nothing, Nothing)
        If Me.rad全部折叠.Checked Then
            Me.OutlookGrid1.CollapseAll()
        Else
            Me.OutlookGrid1.ExpandAll()
        End If
    End Sub

#Region "查询上一个月按钮"
    Public Sub LoadData1(ByVal itype As Integer)

        Dim strBegin As String = Me.dt开始时间.Value.Year.ToString("0000") + "-" + Me.dt开始时间.Value.Month.ToString("00") + "-" + Me.dt开始时间.Value.Day.ToString("00")
        Dim strEnd As String = Me.dt结束时间.Value.Year.ToString("0000") + "-" + Me.dt结束时间.Value.Month.ToString("00") + "-" + Me.dt结束时间.Value.Day.ToString("00")

        If strBegin > strEnd Then
            ShowMessageBox("开始日期大于结束日期")
            dt开始时间.Focus()
            Return
        End If

        If txt支出关联1.Tag.ToString.Trim = "" Then
            ShowMessageBox("必须选择一个帐户")
            btn支出关联1.Focus()
            Return
        End If

        txt上期结余.Text = CrashStatus_Backup.Get得到结余金额(ct, txt支出关联1.Tag.ToString.Trim, dt开始时间.Value.AddDays(-1).ToString("yyyy-MM-dd"))

        Dim dtNew As New DataTable
        dtNew.TableName = "收入"

        dtNew.Columns.Add("账户")
        dtNew.Columns.Add("类型")
        dtNew.Columns.Add("金额")
        dtNew.Columns.Add("备注")
        dtNew.Columns.Add("操作时间")
        dtNew.Columns.Add("操作用户")
        dtNew.Columns.Add("类型排序", GetType(Integer))

        Dim dtDataTable As New DataTable

        For Type As Integer = 0 To 6
            Select Case Type
                Case 0 '收入
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine(" SELECT CrashInOut.*  ")
                    sb.AppendLine(" ,'收入' as typename")
                    sb.AppendLine(" ,CrashStatus2.Title as Name2  ")
                    sb.AppendLine(" ,WysInputUser.username username1")
                    sb.AppendLine(" FROM CrashInOut ")
                    sb.AppendLine(" left join CrashStatus CrashStatus2 on CrashStatus2.ID=CrashInOut.CrashStatusID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>='" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + txt支出关联1.Tag.ToString.Trim + "'")
                    sb.AppendLine(" AND CrashInOut.type = 0  ")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Merge(dtDataTable1)
                Case 1 '支出
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine(" SELECT CrashInOut.*  ")
                    sb.AppendLine(" ,'直接支出' as typename")
                    sb.AppendLine(" ,CrashStatus2.Title as Name2  ")
                    sb.AppendLine(" ,WysInputUser.username username1")
                    sb.AppendLine(" FROM CrashInOut ")
                    sb.AppendLine(" left join CrashStatus CrashStatus2 on CrashStatus2.ID=CrashInOut.CrashStatusID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>='" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + txt支出关联1.Tag.ToString.Trim + "'")
                    sb.AppendLine(" AND CrashInOut.type = 1  ")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Merge(dtDataTable1)
                Case 2 '转帐 
                    '转出
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine(" SELECT CrashInOut.*  ")
                    sb.AppendLine(" ,'转帐-出' as typename")
                    sb.AppendLine(" ,CrashStatus1.Title as Name2  ")
                    sb.AppendLine(" ,WysInputUser.username username1")
                    sb.AppendLine(" FROM CrashInOut ")
                    sb.AppendLine(" left join CrashStatus CrashStatus1 on CrashStatus1.ID=CrashInOut.InOutID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>='" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.InOutID ='" + txt支出关联1.Tag.ToString.Trim + "'")
                    sb.AppendLine(" AND CrashInOut.type =2")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Merge(dtDataTable1)

                    '转入
                    sb.Length = 0
                    sb.AppendLine(" SELECT CrashInOut.*  ")
                    sb.AppendLine(" ,'转帐-入' as typename")
                    sb.AppendLine(" ,CrashStatus2.Title as Name2  ")
                    sb.AppendLine(" ,WysInputUser.username username1")
                    sb.AppendLine(" FROM CrashInOut ")
                    sb.AppendLine(" left join CrashStatus CrashStatus2 on CrashStatus2.ID=CrashInOut.CrashStatusID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>='" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + txt支出关联1.Tag.ToString.Trim + "'")
                    sb.AppendLine(" AND CrashInOut.type =2")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    dtDataTable1 = ct.FillTable(sb.ToString)
                    dtDataTable.Merge(dtDataTable1)

                Case 3
                    '借给别人钱
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine(" SELECT CrashInOut.*  ")
                    sb.AppendLine(" ,'借钱-借出' as typename")
                    sb.AppendLine(" ,CrashStatus1.Title as Name2  ")
                    sb.AppendLine(" ,WysInputUser.username username1")
                    sb.AppendLine(" FROM CrashInOut ")
                    sb.AppendLine(" left join CrashStatus CrashStatus1 on CrashStatus1.ID=CrashInOut.CrashStatusID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" inner join WysBorrowLend WysBorrowLend on WysBorrowLend.ID=CrashInOut.InOutID ")
                    sb.AppendLine(" and WysBorrowLend.type=0")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>='" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + txt支出关联1.Tag.ToString.Trim + "'")
                    sb.AppendLine(" AND CrashInOut.type = 3")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Merge(dtDataTable1)

                    '借别人钱
                    sb.Length = 0
                    sb.AppendLine(" SELECT CrashInOut.*  ")
                    sb.AppendLine(" ,'借钱-借入' as typename")
                    sb.AppendLine(" ,CrashStatus2.Title as Name2  ")
                    sb.AppendLine(" ,WysInputUser.username username1")
                    sb.AppendLine(" FROM CrashInOut ")
                    sb.AppendLine(" left join CrashStatus CrashStatus2 on CrashStatus2.ID=CrashInOut.CrashStatusID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" inner join WysBorrowLend WysBorrowLend on WysBorrowLend.ID=CrashInOut.InOutID ")
                    sb.AppendLine(" and WysBorrowLend.type=2")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>='" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + txt支出关联1.Tag.ToString.Trim + "'")
                    sb.AppendLine(" AND CrashInOut.type = 3")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    dtDataTable1 = ct.FillTable(sb.ToString)
                    dtDataTable.Merge(dtDataTable1)
                Case 4
                    '别人还钱
                    Dim sb As New System.Text.StringBuilder
                    sb.AppendLine(" SELECT CrashInOut.*  ")
                    sb.AppendLine(" ,'还钱-还入' as typename")
                    sb.AppendLine(" ,CrashStatus1.Title as Name2  ")
                    sb.AppendLine(" ,WysInputUser.username username1")
                    sb.AppendLine(" FROM CrashInOut ")
                    sb.AppendLine(" left join CrashStatus CrashStatus1 on CrashStatus1.ID=CrashInOut.CrashStatusID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" inner join WysBorrowLend WysBorrowLend on WysBorrowLend.LendBorrowUserName=CrashInOut.InOutID ")
                    sb.AppendLine(" and WysBorrowLend.type= 1 ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>='" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + txt支出关联1.Tag.ToString.Trim + "'")
                    sb.AppendLine(" AND CrashInOut.type = 4")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    Dim dtDataTable1 As DataTable = ct.FillTable(sb.ToString)
                    dtDataTable.Merge(dtDataTable1)

                    '还给别人钱
                    sb.Length = 0
                    sb.AppendLine(" SELECT CrashInOut.*  ")
                    sb.AppendLine(" ,'还钱-还出' as typename")
                    sb.AppendLine(" ,CrashStatus2.Title as Name2  ")
                    sb.AppendLine(" ,WysInputUser.username username1")
                    sb.AppendLine(" FROM CrashInOut ")
                    sb.AppendLine(" left join CrashStatus CrashStatus2 on CrashStatus2.ID=CrashInOut.CrashStatusID ")
                    sb.AppendLine(" LEFT JOIN WysInputUser ")
                    sb.AppendLine(" ON CrashInOut.username=WysInputUser.id ")
                    sb.AppendLine(" inner join WysBorrowLend WysBorrowLend on WysBorrowLend.LendBorrowUserName=CrashInOut.InOutID ")
                    sb.AppendLine(" and WysBorrowLend.type= 3 ")
                    sb.AppendLine(" WHERE date(CrashInOut.DATETIME)>='" + strBegin + "'")
                    sb.AppendLine(" AND date(CrashInOut.DATETIME)<='" + strEnd + "'")
                    sb.AppendLine(" AND CrashInOut.CrashStatusID ='" + txt支出关联1.Tag.ToString.Trim + "'")
                    sb.AppendLine(" AND CrashInOut.type = 4")
                    sb.AppendLine(" order by CrashInOut.CrashStatusID,CrashInOut.DATETIME desc")

                    dtDataTable1 = ct.FillTable(sb.ToString)
                    dtDataTable.Merge(dtDataTable1)
                Case 5
                    ''冲帐
                Case 6
            End Select
        Next

        For Each dr As DataRow In dtDataTable.Rows
            dtNew.Rows.Add(New Object() {转换对象字符串(dr("Name2")), 转换对象字符串(dr("typename")), 转换对象字符串(dr("CrashStatusCount")), _
                                                                    转换对象字符串(dr("MEMO")), 转换日期字符串(dr("DATETIME")), 转换对象字符串(dr("USERNAME1")), 转换对象字符串(dr("type"))})
        Next

        Dim dvNew As DataView = dtNew.DefaultView
        dvNew.Sort = "类型排序"
        dtNew = dvNew.ToTable

        Me.OutlookGrid1.AutoGenerateColumns = False
        Me.OutlookGrid1.BindData(dtNew, "收入")
        Me.OutlookGrid1.Columns(0).Visible = False

    End Sub

    Private Function 转换对象字符串(ByVal ob As Object) As String
        If ob Is DBNull.Value Then
            Return ""
        End If
        Return ob.ToString
    End Function

    Private Function 转换日期字符串(ByVal ob As Object) As String
        Dim dt As Date = Date.Parse(ob.ToString())
        Return dt.ToString("yyyy年MM月dd日")
    End Function

    Private Function 转换日期字符串月(ByVal ob As Object) As String
        Dim dt As Date = Date.Parse(ob.ToString())
        Return dt.ToString("yyyy年MM月")
    End Function
#End Region

#Region "重载设置窗体函数 A-2010-09-02"

    Public Overrides Function 是否显示设置窗体数据行数() As Boolean
        Return False
    End Function
#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        LoadData1(0)
        OutUpdat_Click(Nothing, Nothing)
        rad全部折叠_Click(Nothing, Nothing)
    End Sub

    Private Sub OutUpdat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If Me.OutlookGrid1.RowCount = 0 Then Exit Sub
        Dim al As New List(Of DataGridViewColumn)
        Dim al1 As New List(Of ListSortDirection)

        If al.Count = 0 Then
            al.Add(Me.OutlookGrid1.Columns("类型"))
            al1.Add(ListSortDirection.Ascending)
        End If

        OutlookGrid1.GroupTemplate.Column = al.ToArray
        OutlookGrid1.Sort(al.ToArray)

    End Sub

    Private Sub OutlookGrid1_SGT(ByRef content As System.String, ByVal olg As Wys.Control.CSharp.OutlookGridRow, ByVal RowIndex As System.Collections.Generic.List(Of Integer)) Handles OutlookGrid1.SGT
        If Not olg.IsLastRow Then
            Dim 金额 As Decimal = 0
            For Each ri As Integer In RowIndex
                Select Case CStr(OutlookGrid1.Rows(ri).Cells("类型").Value)
                    Case "收入"
                        金额 += CDec(OutlookGrid1.Rows(ri).Cells("金额").Value)
                    Case "转帐-入"
                        金额 += CDec(OutlookGrid1.Rows(ri).Cells("金额").Value)
                    Case "借钱-借入"
                        金额 += CDec(OutlookGrid1.Rows(ri).Cells("金额").Value)
                    Case "还钱-还入"
                        金额 += CDec(OutlookGrid1.Rows(ri).Cells("金额").Value)
                    Case "直接支出"
                        金额 -= CDec(OutlookGrid1.Rows(ri).Cells("金额").Value)
                    Case "转帐-出"
                        金额 -= CDec(OutlookGrid1.Rows(ri).Cells("金额").Value)
                    Case "借钱-借出"
                        金额 -= CDec(OutlookGrid1.Rows(ri).Cells("金额").Value)
                    Case "还钱-还出"
                        金额 -= CDec(OutlookGrid1.Rows(ri).Cells("金额").Value)
                End Select
                '金额 += CDec(Me.OutlookGrid1.Rows(ri).Cells("金额").Value)
            Next

            content = olg.Group.Value.ToString.Trim & New String(" ", (60 - 得到GB2312编码.GetByteCount(olg.Group.Value.ToString.Trim))) & "  合计：" & 金额.ToString
        Else
            Dim 总金额 As Decimal = 0
            For Each dr As DataGridViewRow In Me.OutlookGrid1.Rows
                Select Case CStr(dr.Cells("类型").Value)
                    Case "收入"
                        总金额 += CDec(dr.Cells("金额").Value)
                    Case "转帐-入"
                        总金额 += CDec(dr.Cells("金额").Value)
                    Case "借钱-借入"
                        总金额 += CDec(dr.Cells("金额").Value)
                    Case "还钱-还入"
                        总金额 += CDec(dr.Cells("金额").Value)
                    Case "直接支出"
                        总金额 -= CDec(dr.Cells("金额").Value)
                    Case "转帐-出"
                        总金额 -= CDec(dr.Cells("金额").Value)
                    Case "借钱-借出"
                        总金额 -= CDec(dr.Cells("金额").Value)
                    Case "还钱-还出"
                        总金额 -= CDec(dr.Cells("金额").Value)
                End Select
                '总金额 += CDec(dr.Cells("金额").Value)
            Next
            content = New String(" ", 60) & " 余额：" & 总金额.ToString & " 结余:" & CStr(CDec(txt上期结余.Text) - Math.Abs(总金额))

        End If
    End Sub

    Private Sub rad全部折叠_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rad全部折叠.Click, rad全部展开.Click
        If Me.rad全部折叠.Checked Then
            Me.OutlookGrid1.CollapseAll()
        Else
            Me.OutlookGrid1.ExpandAll()
        End If
    End Sub

    Private Sub OutlookGrid1_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OutlookGrid1.DoubleClick
        If Me.OutlookGrid1.SelectedRows.Count = 0 Then Exit Sub
        Dim id As String = OutlookGrid1.SelectedRows(0).Cells(0).Value.ToString
        If id.Length = 36 Then
            Using frm As AccountToAccount = New AccountToAccount
                frm.参数列表.Add("ID", id)
                frm.模式 = Wys.Control.Vb.InsertFormBase.模式型.修正
                frm.iType = Me.iType
                frm.strName = Me.strName
                frm.ShowDialog()
                If frm.bAdd Then
                    LoadData1(0)
                    OutUpdat_Click(Nothing, Nothing)
                End If
            End Using
        End If
    End Sub

    Public Overrides Sub 保存窗体数据到数据库(ByRef dtData As System.Data.DataTable)
        '年
        dtData.Rows.Add(New String() {"开始时间", "", dt开始时间.Value.ToString("yyyy-MM-dd")})
        dtData.Rows.Add(New String() {"结束时间", "", dt结束时间.Value.ToString("yyyy-MM-dd")})

        If Me.rad全部展开.Checked Then
            dtData.Rows.Add(New String() {"全部展开", "", "是"})
        Else
            dtData.Rows.Add(New String() {"全部展开", "", "否"})
        End If

        dtData.Rows.Add(New String() {"帐户ID", "", txt支出关联1.Tag.ToString.Trim})
        dtData.Rows.Add(New String() {"帐户名称", "", txt支出关联1.Text.Trim})

        MyBase.保存窗体数据到数据库(dtData)

    End Sub

    Public Overrides Sub 存取数据库数据到窗体(ByVal dtDbData As System.Data.DataTable)

        If dtDbData.Rows.Count > 0 Then
            '年
            dt开始时间.Value = 查找数据("开始时间", "", Now.ToString("yyyy-MM-dd"), dtDbData)
            dt结束时间.Value = 查找数据("结束时间", "", Now.ToString("yyyy-MM-dd"), dtDbData)

            rad全部展开.Checked = IIf(查找数据("全部展开", "", "是", dtDbData) = "是", True, False)
            Me.rad全部折叠.Checked = IIf(查找数据("全部展开", "", "否", dtDbData) = "否", True, False)

            txt支出关联1.Tag = 查找数据("帐户ID", "", "1", dtDbData)
            txt支出关联1.Text = 查找数据("帐户名称", "", "", dtDbData)

        End If
        MyBase.存取数据库数据到窗体(dtDbData)
    End Sub

    Private Sub OutlookGrid1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles OutlookGrid1.CellContentClick

    End Sub

    Private Sub btn支出关联1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn支出关联1.Click
        Using frm As Wys.Control.Vb.ListForm = New Wys.Control.Vb.ListForm
            frm.Text = "账户一览"
            frm.strName = Me.strName
            frm.iType = Me.iType
            frm.SQL = "select ID,TITLE as 名称,Count as 金额,MEMO AS 备注 from CrashStatus order by 名称"
            Dim 显示列 As New StringCollection
            显示列.Add("名称")
            显示列.Add("金额")
            显示列.Add("备注")
            frm.Width = 300
            frm.Height = 300
            frm.显示列 = 显示列
            frm.ShowDialog()
            If frm.返回值.Count > 0 Then
                If CType(sender, Button) Is Me.btn支出关联1 Then
                    txt支出关联1.Tag = CStr(frm.返回值(0))
                    txt支出关联1.Text = CStr(frm.返回值(1))
                    Button1_Click(Nothing, Nothing)
                End If
            End If
        End Using
    End Sub
End Class
